package com.hhf.ds.service;

import com.hhf.ds.util.ImportUtils;
import com.hhf.ds.util.SqlParser;
import net.sf.jsqlparser.JSQLParserException;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Service
public class ImportService {
    @Autowired
    private RptService rptService;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private String insertSqlSeg = "insert into ${tableName}(${columns}) values ";
    private String updateSqlSeg = "update ${tableName} set ${updateColsSeg} where ";

    @Transactional(rollbackFor = RuntimeException.class)
    public void execImp(String rptCode, String filePath) throws IOException, JSQLParserException {
        String sql = getInsertAllSql(rptCode, filePath);
        jdbcTemplate.execute(sql);
    }

    // 获取批量插入sql语句
    public String getInsertAllSql(String rptCode, String filePath) throws JSQLParserException, IOException {
        String s = getInsertSqlSeg(rptCode) + getInsertSqlValuesSeg(filePath);
        return s;
    }

    public String getRptSqlByRptCode(String rptCode) {
        ByteArrayInputStream bis = rptService.getRptExprStream(rptCode);
        SqlParser sqlParser = new SqlParser(rptCode, bis);
        HashMap<Object, Object> obj = new HashMap<>();
        obj.put("rptCode", rptCode);
        return sqlParser.parseSQL(obj);
    }

    private String getInsertSqlSeg(String rptCode) throws JSQLParserException {
        String sql = getRptSqlByRptCode(rptCode);
        String selectColNames = ImportUtils.getSelectColNames(sql);
        List<String> selectTables = ImportUtils.getSelectTables(sql);
        String insertSqlSeg = this.insertSqlSeg.replace("${tableName}", selectTables.get(0)).replace("${columns}", selectColNames);
        return insertSqlSeg;
    }

    private String getInsertSqlValuesSeg(String filePath) throws IOException {
        List<List<String>> excelDataList = ImportUtils.allExcelData(filePath);
        List<String> rowData = new ArrayList();
        int i=0;
        for(List<String> rowList : excelDataList) {
            i++;
            if(i==1) {
                // 跳过excel表头
                continue;
            }
            rowData.add(convertListToString(rowList,"'","'",","));
        }
        return convertListToString(rowData, "(", ")", ",");
    }

    private static String convertListToString(List<String> strlist, String prefix,String suffix, String splitFlag){
        StringBuffer sb = new StringBuffer();
        if(CollectionUtils.isNotEmpty(strlist)){
            for (int i=0;i<strlist.size();i++) {
                if(i==0) {
                    sb.append(prefix).append(strlist.get(i)).append(suffix);
                } else {
                    sb.append(splitFlag).append(prefix).append(strlist.get(i)).append(suffix);
                }
            }
        }
        return sb.toString();
    }

}
